Simplest Example

Intro

traits-sqlalchemy provides utilities for mapping HasTraits classes to a relational database using SQLAlchemy. This example demonstrates using traits-sqlalchemy to create a simple, one-table mapping with a HasDBTraits class.



In [163]:

    
import sqlalchemy as sql
from sqlalchemy import orm
import traitsql as tsql
from traits.api import TraitError

Create Schema



In [164]:

    
metadata = sql.MetaData()
foo = sql.Table('foo', metadata,
                sql.Column('id', sql.Integer, primary_key=True),
                sql.Column('int', sql.Integer),
                sql.Column('float', sql.Float),
                sql.Column('string', sql.String(16), unique = True))

Create The Class



In [152]:

    
class Foo(tsql.HasDBTraits):
    id = tsql.DBIntKey
    int = tsql.DBInt
    float = tsql.DBFloat
    string = tsql.DBStr
    
    def __repr__(self):
        return 'Foo(id=%i, int=%i, float=%f, string=%s)'%(
                self.id, self.int, self.float, self.string)

Map it!



In [153]:

    
orm.mapper(Foo, foo)









    Out[153]:





<Mapper at 0x7f69b70f8f50; Foo>

Test it

Connecting

Create the engine and bind it to our metadata.
Create a connection to the database.
Create a Session



In [154]:

    
db = sql.create_engine('sqlite:///:memory:')
metadata.bind = db
metadata.create_all()
conn = db.connect()
session = orm.sessionmaker()()

Instantiate a class and commit it



In [155]:

    
session.add(Foo(int=10, float=20.0, string='foo'))
session.commit()
foo = session.query(Foo).first()
foo









    Out[155]:





Foo(id=1, int=10, float=20.000000, string=foo)

Traits does it's validation as expected



In [156]:

    
try:
    foo.int ='int'
except(TraitError) as e:
    print(e)









    



The 'int' trait of a Foo instance must be an integer (int or long) or None, but a value of 'int' <type 'str'> was specified.

Edit existing entries values and commit



In [157]:

    
foo.int = 12
session.commit()
session.query(Foo).first()









    Out[157]:





Foo(id=1, int=12, float=20.000000, string=foo)



In [158]:

    
session.add(Foo(int=11, float=20.0, string='dank'))
session.add(Foo(int=12, float=40.0, string='stank'))
session.add(Foo(int=13, float=30.0, string='wank'))
session.add(Foo(int=14, float=10.0, string='pank'))
session.commit()



In [159]:

    
q = session.query(Foo)
q.all()









    Out[159]:





[Foo(id=1, int=12, float=20.000000, string=foo),
 Foo(id=2, int=11, float=20.000000, string=dank),
 Foo(id=3, int=12, float=40.000000, string=stank),
 Foo(id=4, int=13, float=30.000000, string=wank),
 Foo(id=5, int=14, float=10.000000, string=pank)]



In [160]:

    
session.add(Foo(int=14, float=10.0, string='pank'))

try: 
    session.commit()
except(sql.exc.IntegrityError) as e:
    print(e)









    



(IntegrityError) UNIQUE constraint failed: foo.string u'INSERT INTO foo (int, float, string) VALUES (?, ?, ?)' (14, 10.0, 'pank')

Close connection



In [161]:

    
conn.close()
metadata.bind = None